package com.zx.mes.poi.utils;

import org.apache.poi.hssf.usermodel.*;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class ExcelUtils {

    private static final int PAGE_NUM = 65500;


    /**
     * 非空判断
     *
     * @param obj obj
     * @return boolean true/false
     */
    private static boolean isEmptyOrNull(Object obj) {
        if (obj == null) {
            return true;
        }
        if (obj instanceof Map) {
            return ((Map) obj).isEmpty();
        } else if (obj instanceof List) {
            return ((List) obj).isEmpty();
        }
        return obj.toString().isEmpty();
    }


    /**
     * 获取工作簿
     *
     * @param OneSheetRowDataInMapAndAddToList 数据集合
     * @param sheetColumnNameMap               列名集合
     * @return HSSFWorkbook 类型
     * @throws Exception 异常
     */
    public static HSSFWorkbook getHSSFWorkbook(List<Map<String, Object>> OneSheetRowDataInMapAndAddToList, Map<String, String> sheetColumnNameMap) {

        HSSFWorkbook workbook = new HSSFWorkbook();
        //对集合进行非空判断
        if (isEmptyOrNull(OneSheetRowDataInMapAndAddToList) || isEmptyOrNull(sheetColumnNameMap)) {
            workbook.createSheet();
            return workbook;
        }

        int pageNum = PAGE_NUM;//每页数量
        int OneSheetRowDataInMapAndAddToListSize = OneSheetRowDataInMapAndAddToList.size();//数据数量
        int sheetPageSize = (OneSheetRowDataInMapAndAddToListSize % pageNum > 0) ? (OneSheetRowDataInMapAndAddToListSize / pageNum + 1) : (OneSheetRowDataInMapAndAddToListSize / pageNum);//导出页数

        HSSFCellStyle cellStyle;//单元格样式
        HSSFSheet sheet;//页面
        HSSFFont font = workbook.createFont();//字体

        font.setFontHeightInPoints((short) 11);//设置字号
        cellStyle = workbook.createCellStyle();

        cellStyle.setFont(font);
        cellStyle.setFillPattern(HSSFCellStyle.FINE_DOTS);

        Set<String> sheetColumnNameKeySet = sheetColumnNameMap.keySet();
        HSSFRow sheetRow;//单元格行
        Object sheetColumnValue;
        String sheetColumnNameValue;
        Map<String, Object> OneSheetRowDataInMap;

        for (int sheetPageIndex = 0; sheetPageIndex < sheetPageSize; sheetPageIndex++) {
            sheet = workbook.createSheet();
            workbook.setSheetName(sheetPageIndex, (String.valueOf((sheetPageIndex + 1))));
            sheetRow = sheet.createRow(0);
            for (String sheetColumnNameKey : sheetColumnNameKeySet) {
                sheetColumnNameValue = sheetColumnNameMap.get(sheetColumnNameKey);
                createStringCell(sheetRow, sheetRow.getLastCellNum() == -1 ? 0 : sheetRow.getLastCellNum(), sheetColumnNameValue, cellStyle);
            }
            for (int OneSheetRowDataInMapAndAddToListSizeIndex = 0; OneSheetRowDataInMapAndAddToListSizeIndex < OneSheetRowDataInMapAndAddToListSize; OneSheetRowDataInMapAndAddToListSizeIndex++) {
                OneSheetRowDataInMap = OneSheetRowDataInMapAndAddToList.get(OneSheetRowDataInMapAndAddToListSizeIndex);
                sheetRow = sheet.createRow(OneSheetRowDataInMapAndAddToListSizeIndex + 1 - (pageNum * sheetPageIndex));
                for (String sheetColumnNameKey : sheetColumnNameKeySet) {
                    sheetColumnValue = OneSheetRowDataInMap.get(sheetColumnNameKey);
                    createStringCell(sheetRow, sheetRow.getLastCellNum() == -1 ? 0 : sheetRow.getLastCellNum(), sheetColumnValue, cellStyle);
                }
            }
        }
        return workbook;

    }

    /**
     * 创建单元格
     *
     * @param row       行
     * @param index     列
     * @param cellValue 列里的值
     * @param cellStyle excel css
     */
    private static void createStringCell(HSSFRow row, short index, Object cellValue, HSSFCellStyle cellStyle) {
        HSSFCell cell = row.createCell(index);
        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        if (!isEmptyOrNull(cellValue) && cellValue instanceof Date) {
            cellValue = doDate2String((Date) cellValue);
        }
        cell.setCellValue(null2Empty(cellValue));
    }


    /**
     * 将日期转化为String
     *
     * @param confirmDate 时间
     * @param patten      时间格式化 格式
     * @return String str
     */
    private static String doDate2String(Date confirmDate, String... patten) {
        if (confirmDate == null) {
            return "";
        }
        SimpleDateFormat sdf;
        if (patten == null || patten.length == 0) {
            sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        } else {
            sdf = new SimpleDateFormat(patten[0]);
        }
        return sdf.format(new Date(confirmDate.getTime()));
    }

    /**
     * 将null转化为Empty
     *
     * @param areaId 值
     * @return String str
     */
    private static String null2Empty(Object areaId) {
        return areaId == null ? "" : areaId.toString();
    }
}

